﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _01_SqlCommand_操作数据库
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //创建连接字符串构建器,用来提供连接SQLServer数据库
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "DS16PLUS";
            builder.InitialCatalog = "TEST5";
            builder.UserID = "sa";
            builder.Password = "123456";

            //创建连接对象
            using (SqlConnection connection = new SqlConnection(builder.ToString()))
            {
                // 创建一个SQL命令对象
                SqlCommand command = new SqlCommand("SELECT * FROM UserTable", connection);
                try
                {
                    //打开连接
                    connection.Open();
                    //执行SQL命令：返回一个读取器，用来读取数据
                    SqlDataReader reader = command.ExecuteReader();
                    Console.WriteLine("========================UserTable表格中的所有用户========================");
                    //读取数据
                    while (reader.Read())//读取一行数据，如果读取到数据返回true，否则返回false
                    {
                        Console.WriteLine(reader["userID"] + " " + reader["userName"] + " " + reader["userPassWord"] + reader["userPower"] );
                    }
                    reader.Close();//关闭读取器
                    //向表格插入数据: INSERT INTO UserTable(userName,userPassWord,userPower) VALUES('user2','123456','用户')
                    Console.WriteLine("========================UserTable表格中插入用户========================");
                    //提示用户插入数据
                    Console.WriteLine("账号:user2");
                    Console.WriteLine("密码:123456");
                    Console.WriteLine("权限:用户");
                    //插入数据库前检查是否已经已经存在该用户
                    command.CommandText = "SELECT * FROM UserTable WHERE userName='user2'";
                    reader = command.ExecuteReader();//执行查询
                    //如果查询到数据，说明已经存在该用户，不再插入并提示用户
                    if (reader.Read()) //读取一行判断是否有内容
                    {
                        Console.WriteLine("已经存在该用户，不再插入");
                    }
                    else
                    {
                        //关闭command的操作
                        reader.Close();

                        //插入数据
                        command.CommandText = "INSERT INTO UserTable(userName,userPassWord,userPower) VALUES('user2','123456','用户')";
                        int result = command.ExecuteNonQuery();//执行插入操作,返回受影响的行数
                        if (result > 0)
                        {
                            Console.WriteLine("插入成功!");
                        }
                        else
                        {
                            Console.WriteLine("插入失败");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("连接失败:" + ex.Message);
                }
            }

            Console.Read();

        }
    }
}
